
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/01/2011 12:52:47
-- Generated from EDMX file: D:\@yuxinyang\v4\YiDeSoft\BigTrunk\EzDesk-Dev\Services\Apps\EzDesk.Apps.Data\ProjectModule\EzDeskProject.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [EzDeskProject];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ProjectMemberInProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MemberInProjectSet] DROP CONSTRAINT [FK_ProjectMemberInProject];
GO
IF OBJECT_ID(N'[dbo].[FK_WorkitemProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkitemSet] DROP CONSTRAINT [FK_WorkitemProject];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskWorkitem]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkitemSet] DROP CONSTRAINT [FK_TaskWorkitem];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TaskSet] DROP CONSTRAINT [FK_TaskProject];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskTask]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TaskSet] DROP CONSTRAINT [FK_TaskTask];
GO
IF OBJECT_ID(N'[dbo].[FK_TaskTask1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TaskSet] DROP CONSTRAINT [FK_TaskTask1];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[WorkitemSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkitemSet];
GO
IF OBJECT_ID(N'[dbo].[DailyWorksSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DailyWorksSet];
GO
IF OBJECT_ID(N'[dbo].[ProjectSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProjectSet];
GO
IF OBJECT_ID(N'[dbo].[MemberInProjectSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MemberInProjectSet];
GO
IF OBJECT_ID(N'[dbo].[TaskSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TaskSet];
GO
IF OBJECT_ID(N'[dbo].[ProjectTemplateSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProjectTemplateSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'WorkitemSet'
CREATE TABLE [dbo].[WorkitemSet] (
    [Id] uniqueidentifier  NOT NULL,
    [OwnerId] uniqueidentifier  NULL,
    [CreatorId] uniqueidentifier  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [IsFinished] bit  NOT NULL,
    [FinishOn] datetime  NULL,
    [Points] int  NOT NULL,
    [ProjectId] uniqueidentifier  NULL,
    [IsTrace] bit  NOT NULL,
    [PlanOn] datetime  NULL,
    [StatusValue] int  NOT NULL,
    [TaskId] uniqueidentifier  NULL,
    [UpdateOn] datetime  NULL
);
GO

-- Creating table 'DailyWorksSet'
CREATE TABLE [dbo].[DailyWorksSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [Day] datetime  NULL,
    [DailyWork] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ProjectSet'
CREATE TABLE [dbo].[ProjectSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Code] nvarchar(max)  NOT NULL,
    [ChargePersonId] uniqueidentifier  NULL,
    [Description] nvarchar(max)  NOT NULL,
    [CreateOn] datetime  NOT NULL,
    [MemberString] nvarchar(max)  NOT NULL,
    [PlanOn] datetime  NULL,
    [FinishOn] datetime  NULL,
    [StartOn] datetime  NULL,
    [StatusValue] int  NOT NULL,
    [CreatorId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'MemberInProjectSet'
CREATE TABLE [dbo].[MemberInProjectSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [ProjectId] uniqueidentifier  NULL
);
GO

-- Creating table 'TaskSet'
CREATE TABLE [dbo].[TaskSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [ChargePersonId] uniqueidentifier  NULL,
    [StartOn] datetime  NULL,
    [Duration] float  NOT NULL,
    [ProjectId] uniqueidentifier  NOT NULL,
    [ParentTaskId] uniqueidentifier  NULL,
    [MasterTaskId] uniqueidentifier  NULL
);
GO

-- Creating table 'ProjectTemplateSet'
CREATE TABLE [dbo].[ProjectTemplateSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [TemplateData] nvarchar(max)  NOT NULL,
    [CreateOn] datetime  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'WorkitemSet'
ALTER TABLE [dbo].[WorkitemSet]
ADD CONSTRAINT [PK_WorkitemSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DailyWorksSet'
ALTER TABLE [dbo].[DailyWorksSet]
ADD CONSTRAINT [PK_DailyWorksSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProjectSet'
ALTER TABLE [dbo].[ProjectSet]
ADD CONSTRAINT [PK_ProjectSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MemberInProjectSet'
ALTER TABLE [dbo].[MemberInProjectSet]
ADD CONSTRAINT [PK_MemberInProjectSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TaskSet'
ALTER TABLE [dbo].[TaskSet]
ADD CONSTRAINT [PK_TaskSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProjectTemplateSet'
ALTER TABLE [dbo].[ProjectTemplateSet]
ADD CONSTRAINT [PK_ProjectTemplateSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ProjectId] in table 'MemberInProjectSet'
ALTER TABLE [dbo].[MemberInProjectSet]
ADD CONSTRAINT [FK_ProjectMemberInProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[ProjectSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectMemberInProject'
CREATE INDEX [IX_FK_ProjectMemberInProject]
ON [dbo].[MemberInProjectSet]
    ([ProjectId]);
GO

-- Creating foreign key on [ProjectId] in table 'WorkitemSet'
ALTER TABLE [dbo].[WorkitemSet]
ADD CONSTRAINT [FK_WorkitemProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[ProjectSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WorkitemProject'
CREATE INDEX [IX_FK_WorkitemProject]
ON [dbo].[WorkitemSet]
    ([ProjectId]);
GO

-- Creating foreign key on [TaskId] in table 'WorkitemSet'
ALTER TABLE [dbo].[WorkitemSet]
ADD CONSTRAINT [FK_TaskWorkitem]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[TaskSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskWorkitem'
CREATE INDEX [IX_FK_TaskWorkitem]
ON [dbo].[WorkitemSet]
    ([TaskId]);
GO

-- Creating foreign key on [ProjectId] in table 'TaskSet'
ALTER TABLE [dbo].[TaskSet]
ADD CONSTRAINT [FK_TaskProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[ProjectSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskProject'
CREATE INDEX [IX_FK_TaskProject]
ON [dbo].[TaskSet]
    ([ProjectId]);
GO

-- Creating foreign key on [ParentTaskId] in table 'TaskSet'
ALTER TABLE [dbo].[TaskSet]
ADD CONSTRAINT [FK_TaskTask]
    FOREIGN KEY ([ParentTaskId])
    REFERENCES [dbo].[TaskSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTask'
CREATE INDEX [IX_FK_TaskTask]
ON [dbo].[TaskSet]
    ([ParentTaskId]);
GO

-- Creating foreign key on [MasterTaskId] in table 'TaskSet'
ALTER TABLE [dbo].[TaskSet]
ADD CONSTRAINT [FK_TaskTask1]
    FOREIGN KEY ([MasterTaskId])
    REFERENCES [dbo].[TaskSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTask1'
CREATE INDEX [IX_FK_TaskTask1]
ON [dbo].[TaskSet]
    ([MasterTaskId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------